﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.Reflection;
using System.IO;
using System.Runtime.InteropServices;
using System.Data.EntityClient;
using System.Data.Metadata.Edm;
using System.Data.Objects.DataClasses;
using System.Data.Objects;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using Microsoft.CSharp;
using System.Collections;

namespace Hdt.TransOcean.Dal
{
    class Dao
    {
        HDTTransOceanEntities context = new HDTTransOceanEntities();
        public Dao()
        { 
             
        }

        public static string GetAutoCode(string tableName)
        {
            using (HDTTransOceanEntities context1 = new HDTTransOceanEntities())
            {
                AutoCode autoCode = (from i in context1.AutoCodes
                                     where i.table_name.Equals(tableName)
                                     select i).ToList().First();
                if (!String.IsNullOrEmpty(autoCode.current_value))
                {
                    return Common.AutoCode(autoCode.current_prefix.ToString().Trim(), autoCode.current_value.ToString().Trim());
                }
                return String.Empty;
            }
        }

        public static string GetAutoCodePrefix(string tableName)
        {
            using (HDTTransOceanEntities context1 = new HDTTransOceanEntities())
            {
                AutoCode autoCode = (from i in context1.AutoCodes
                                     where i.table_name.Equals(tableName)
                                     select i).ToList().First();
                if (!String.IsNullOrEmpty(autoCode.current_value))
                {
                    //return Common.AutoCode(autoCode.current_prefix.ToString().Trim(), autoCode.current_value.ToString().Trim());
                    return autoCode.current_prefix.ToString();
                }
                return String.Empty;
            }
        }
        public static bool ExportExcel(EObject obj, string Filename, List<string[]> list)
        {
            string[] titles = null;

            switch (obj)
            {
                case EObject.Airline:
                    titles = new string[] {"code", "name", "type", "descripton", "deleted" };
                    break;
                case EObject.Customer:
                    titles = new string[] { "code", "name", "telephone","mobile","fax","address", "email", "descripton", "debt_day_num", "tax_code", "type", "used"};
                    break;
                case EObject.Journey:
                    titles = new string[] { "code", "name", "type", "descripton", "deleted" };
                    break;
                case EObject.Item:
                    titles = new string[] { "code", "name", "journey_code", "airline_code", "type", "purchase_price", "purchase_commission", "sale_price", "deleted" };
                    break;
                case EObject.SaleInvoice:
                    titles = new string[] { "code", "name", "type", "descripton", "deleted" };
                    break;
                case EObject.ReturnInvoice:
                    titles = new string[] { "code", "name", "type", "descripton", "deleted" };
                    break;
                case EObject.PurchaseInvoice:
                    titles = new string[] { "code", "name", "type", "descripton", "deleted" };
                    break;
                case EObject.Menu:
                    titles = new string[] { "code", "name", "parent", "level", "order" };
                    break;
                case EObject.Function:
                    titles = new string[] { "name", "order" };
                    break;
                case EObject.MenuFunction:
                    titles = new string[] { "menu_id", "function_id"};
                    break;
                case EObject.Employee:
                    titles = new string[] { "code", "name", "department", "title", "phone", "email", "description", "user", "status", };
                    break;
                case EObject.User:
                    titles = new string[] { "code", "status", "group_id" };
                    break;
                case EObject.Group:
                    titles = new string[] { "name", "role_id", "status"};
                    break;
                case EObject.Role:
                    titles = new string[] { "menu_id", "function_id", "value"};
                    break;
                case EObject.Setting:
                    titles = new string[] { "name", "value", "default_value", "data_type"};
                    break;
            }
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.FileName = Filename;
            saveFileDialog.DefaultExt = ".xls";
            saveFileDialog.Filter = "Excel(*.xls;*.xlsx)|*.xls;*.xlsx|csv file(*.csv)|*.csv";

            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application();
                if (saveFileDialog.CheckFileExists == false)
                {
                 // myStream = saveFileDialog.OpenFile();
                }
                    try
                    {
                        //Opens WorkBook in Excel  
                        Microsoft.Office.Interop.Excel._Workbook xlsWorkBook = xlsApp.Workbooks.Add(Type.Missing);
                        Worksheet xlsWorkSheet = (Worksheet)xlsWorkBook.Worksheets.get_Item(1);
                        //Microsoft.Office.Interop.Excel._Workbook xlsWorkBook = xlsApp.Workbooks.Add(Type.Missing);
                        xlsApp.Visible = true;
                        // get the reference of first sheet. By default its name is Sheet1.
                        // store its reference to worksheet
                        //xlsWorkSheet = xlsWorkBook.Sheets["Sheet1"];
                        //xlsWorkSheet = xlsWorkBook.ActiveSheet;
                        // Export header
                        for (int i = 1; i <= titles.Count(); i++)
                        {
                            xlsWorkSheet.Cells[1, i] = titles[i - 1];
                        }
                        int j = 0;
                        foreach(string [] row in list)
                        {
                            for (int i = 0; i < titles.Count(); i++)
                            { 
                                xlsWorkSheet.Cells[j + 2, i + 1] = row[i];
                            }
                            j++;
                        }

                        xlsWorkBook.SaveAs(saveFileDialog.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        xlsWorkBook.Close();
                        xlsApp.Quit();
                    }
                    catch (Exception e)
                    {
                        throw e;
                    }

                    finally
                    {
                        Marshal.ReleaseComObject(xlsApp);
                        xlsApp = null;
                        GC.Collect();
                        GC.WaitForPendingFinalizers();
                        
                    }
                    return true;
            }
            return false;
        }

        public static bool ImportExcel(EObject obj)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            
            openFileDialog.DefaultExt = ".xls";
            openFileDialog.Filter = "Excel(*.xls;*.xlsx)|*.xls;*.xlsx|csv file(*.csv)|*.csv|All files(*.*)|*.*";

            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application();
                if (openFileDialog.CheckFileExists == false)
                // openFileDialog.OpenFile();
                { }
                try
                {
                    //Opens WorkBook in Excel  
                    Workbook xlsWorkBook = (Workbook)xlsApp.Workbooks.Open(openFileDialog.FileName, null, false);
                    Worksheet xlsWorkSheet = (Worksheet)xlsWorkBook.Worksheets.get_Item(1);
                    //xlsApp.Visible = true;

                    //int i = 2; // begin from row 2nd in excel app

                    Range range = xlsWorkSheet.UsedRange;

                    switch (obj)
                    { 
                        case EObject.Airline:
                             Airline airline;
                            for (int i = 2; i <= range.Rows.Count; i++)
                            {
                                 airline = new Airline();
                                airline.code =(xlsWorkSheet.Cells[i, 1] as Range).Text.ToString();
                                airline.name = (xlsWorkSheet.Cells[i, 2] as Range).Text.ToString();
                                airline.description = (xlsWorkSheet.Cells[i, 3] as Range).Text.ToString();
                                try
                                {
                                    InsertAirLine(airline);
                                }
                                catch (Exception e)
                                {
                                    throw e;
                                }
                            }
                            break;
                        case EObject.Customer:
                             Customer customer;
                            for (int i = 2; i <= range.Rows.Count; i++)
                            {
                                //"code", "name","address","telephone","mobile","fax","email","tax_code","num_debt_date", "description" 
                                customer = new Customer();
                                customer.code = (xlsWorkSheet.Cells[i, 1] as Range).Text.ToString();
                                customer.name = (xlsWorkSheet.Cells[i, 2] as Range).Text.ToString();
                                customer.address = (xlsWorkSheet.Cells[i, 3] as Range).Text.ToString();
                                customer.telephone = (xlsWorkSheet.Cells[i, 3] as Range).Text.ToString();
                                customer.mobile = (xlsWorkSheet.Cells[i, 5] as Range).Text.ToString();
                                customer.fax = (xlsWorkSheet.Cells[i, 6] as Range).Text.ToString();
                                customer.email = (xlsWorkSheet.Cells[i, 7] as Range).Text.ToString();
                                customer.tax_code = (xlsWorkSheet.Cells[i, 8] as Range).Text.ToString();
                                customer.debt_day_num = Int16.Parse((xlsWorkSheet.Cells[i, 9] as Range).Text.ToString());
                                customer.description = (xlsWorkSheet.Cells[i, 10] as Range).Text.ToString();
                                try
                                {
                                    InsertCustomer(customer);
                                }
                                catch (Exception e)
                                {
                                    throw e;
                                }
                            }
                            break;
                        case EObject.Journey:
                             Journey journey;
                            for (int i = 2; i <= range.Rows.Count; i++)
                            {
                                 journey = new Journey();
                                 journey.code = (xlsWorkSheet.Cells[i, 1] as Range).Text.ToString();
                                 journey.name = (xlsWorkSheet.Cells[i, 2] as Range).Text.ToString();
                                 journey.description = (xlsWorkSheet.Cells[i, 3] as Range).Text.ToString();
                                try
                                {
                                    InsertJourney(journey);
                                }
                                catch (Exception e)
                                {
                                    throw e;
                                }
                            }
                            break;
                        case EObject.Item:
                             Item item;
                            for (int i = 2; i <= range.Rows.Count; i++)
                            {
                                item = new Item();
                                item.code = (xlsWorkSheet.Cells[i, 1] as Range).Text.ToString();
                                item.name = (xlsWorkSheet.Cells[i, 2] as Range).Text.ToString();
                                //item.description = (xlsWorkSheet.Cells[i, 3] as Range).Text.ToString();
                                try
                                {
                                    InsertItem(item);
                                }
                                catch (Exception e)
                                {
                                    throw e;
                                }
                            }
                            break;
                        case EObject.SaleInvoice:

                            break;
                        case EObject.ReturnInvoice:

                            break;
                        case EObject.PurchaseInvoice:

                            break;
                        case EObject.User:

                            break;
                        case EObject.Group:

                            break;
                        case EObject.Role:

                            break;                    
                    }

                    //xlsWorkBook.Save();
                    xlsWorkBook.Close();
                    xlsApp.Quit();
                }

                catch(Exception e)
                {
                    throw e;
                }
                finally
                {
                    Marshal.ReleaseComObject(xlsApp);
                    xlsApp = null;
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                }
                return true;
            }
            return false;
        }
       
        public List<Customer> Select()
        {

            var customers = (from c in this.context.Customers
                            select c).ToList<Customer>();
            return customers;
        
        }

        public int Insert(Customer customer)
        {
            try
            {
                context.AddToCustomers(customer);
                context.SaveChanges();
            }
            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                
            }
            return 1;
        }

		public static int InsertTermBeginningDebt(TermBeginningDebt[] invoiceDetails)
        {
            int result = 1;
            int nCount = 0;
            DbTransaction dbTran = null;

            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                try
                {
                    ct.Connection.Open();
                    dbTran = ct.Connection.BeginTransaction();
                    foreach (TermBeginningDebt d in invoiceDetails)
                    {
                        if (d != null)
                        {
                            ct.AddToTermBeginningDebts(d);
                            nCount++;
                        }
                    }
                    ct.SaveChanges();
                    if (nCount == 0)
                    {
                        Exception et = new Exception("không có chi tiết");
                        throw et;
                    }
                    else
                        dbTran.Commit();
                }
                catch (Exception ex)
                {
                    dbTran.Rollback();
                    result = 0;
                    throw ex;
                }
                finally
                {
                    ct.Connection.Close();
                }
                return result;
            }
        }
        public static IList<Invoice> SelectInvoice(string invoiceCode)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var saleInvoice = (from a in ct.Invoices
                                   where a.code.Equals(invoiceCode)
                                   select a).ToList<Invoice>();
                return saleInvoice;
            }
        }

        public static IList<Invoice> SelectInvoice(int invoiceType)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var saleInvoice = new List<Invoice>();
                if (invoiceType == 1)// Sales Invoice
                    saleInvoice = (from a in ct.Invoices
                                   where a.invoice_type.Equals(invoiceType)
                                   select a).ToList<Invoice>();
                if (invoiceType == 2)// Void Invoice
                    saleInvoice = (from a in ct.Invoices
                                   where a.invoice_type.Equals(3)
                                   select a).ToList<Invoice>();
                if (invoiceType == 3)// all
                    saleInvoice = (from a in ct.Invoices
                                   select a).ToList<Invoice>();

                return saleInvoice;
            }
        }

        public static IList<InvoiceDetail> SelectInvoiceDetail(string invoiceCode)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var saleInvoice = (from a in ct.InvoiceDetails
                                   where a.invoice_code.Equals(invoiceCode)
                                   select a).ToList<InvoiceDetail>();
                return saleInvoice;
            }
        }

        public static IList<Airline> SelectAirline()
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var airlines = (from a in ct.Airlines
                                select a).ToList<Airline>();
                return airlines;
            }
        }
        public static IList<Airline> SelectAirline(string code)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var airlines = (from a in ct.Airlines
                                where a.code.Equals(code)
                                select a).ToList<Airline>();
                return airlines;
            }
        }

        public static IList<Airline> SelectArilineByJourneyCode(string journeyCode)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var result = (from i in ct.Items
                              join a in ct.Airlines
                              on i.journey_code equals a.code
                              where i.journey_code.Equals(journeyCode)
                              select a).ToList<Airline>();
                return result;
            }
        }

        public static IList<Customer> SelectCustomer()
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var customer = (from a in ct.Customers
                                select a).ToList<Customer>();
                return customer;
            }
        }

        public static IList<Customer> SelectCustomer(string customerCode)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var customer = (from a in ct.Customers
                                where a.code.Equals(customerCode)
                                select a).ToList<Customer>();
                return customer;
            }
        }
        //public static Customer SelectCustomer(string customerCode)
        //{
        //    using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
        //    {
        //        var customer = (from a in ct.Customers
        //                        where a.code.Equals(customerCode)
        //                        select a);
        //        return customer.First();
        //    }
        //}

        public static IList<Journey> SelectJourney()
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var journey = (from a in ct.Journeys
                               select a).ToList<Journey>();
                return journey;
            }
        }
        public static IList<Journey> SelectJourney(string code)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var journey = (from a in ct.Journeys
                               where a.code.Equals(code)
                               select a).ToList<Journey>();
                return journey;
            }
        }

        public static IList<Item> SelectItem()
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var item = (from a in ct.Items
                            select a).ToList<Item>();
                return item;
            }
        }

        public static IList<Item> SelectItem(string code)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var item = (from a in ct.Items
                            where a.code.Equals(code)
                            select a).ToList<Item>();
                return item;
            }
        }

        public static IList<Item> SelectItem(int type)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var item = (from a in ct.Items
                            where a.type.Equals(type)
                            select a).ToList<Item>();
                return item;
            }
        }

        public static IList<Employee> SelectEmployee()
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var employee = (from a in ct.Employees
                                select a).ToList<Employee>();
                return employee;
            }
        }

        public static IList<Employee> SelectEmployee(string code)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var employee = (from a in ct.Employees
                                where a.code.Equals(code)
                                select a).ToList<Employee>();
                return employee;
            }
        }
        public static IList<Employee> SelectEmployee(int department)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var employee = (from a in ct.Employees
                                where a.department_code.Equals(department)
                                select a).ToList<Employee>();
                return employee;
            }
        }

        public static IList<User> SelectUser()
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var user = (from a in ct.Users
                            select a).ToList<User>();
                return user;
            }
        }
        public static IList<Group> SelectGroup()
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var group = (from a in ct.Groups
                             select a).ToList<Group>();
                return group;
            }
        }

        public static IList<Menu> SelectMenu()
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var menu = (from a in ct.Menus
                            select a).ToList<Menu>();
                return menu;
            }
        }

        public static IList<Menu_Function> SelectMenuFunction()
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var menu_function = (from a in ct.Menu_Function
                                     select a).ToList<Menu_Function>();
                return menu_function;
            }
        }

        public static IList<Role> SelectRole()
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var role = (from a in ct.Roles
                            select a).ToList<Role>();
                return role;
            }
        }

        public static IList<Setting> SelectSetting()
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var setting = (from a in ct.Settings
                               select a).ToList<Setting>();
                return setting;
            }
        }

        public Item SelectItemByJourneyAndAirline(string journeyCode, string airlineCode)
        {
            var item = (from a in context.Items
                        where a.journey_code.Equals(journeyCode) && a.airline_code.Equals(airlineCode)
                        select a).First();
            return item;
        }

        public IEnumerable<Invoice> SelectSaleInvoice()
        {
            var saleInvoice = (from a in context.Invoices
                            select a);
            return saleInvoice;
        }

        public IEnumerable<InvoiceDetail> SelectSaleInvoiceDetail(string invoiceCode)
        {
            var saleInvoiceDetail = (from a in context.InvoiceDetails
                                     where a.invoice_code.Equals(invoiceCode)
                               select a);
            return saleInvoiceDetail;
        }

        public static bool InsertAutoCode(AutoCode autoCode)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                try
                {
                    ct.AddToAutoCodes(autoCode);
                    ct.SaveChanges();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return true;
            }
        }

        public static bool InsertAirLine(Airline airline)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                try
                {
                    ct.AddToAirlines(airline);
                    ct.SaveChanges();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return true;
            }
        }

        public static bool InsertCustomer(Customer customer)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                try
                {
                    ct.AddToCustomers(customer);
                    ct.SaveChanges();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return true;
            }
        }

        public static bool InsertItem(Item item)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                try
                {
                    ct.AddToItems(item);
                    ct.SaveChanges();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return true;
            }
        }

        public static bool InsertJourney(Journey journey)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                try
                {
                    ct.AddToJourneys(journey);
                    ct.SaveChanges();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return true;
            }
        }
        
        public static bool InsertEmployee(Employee employee)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                try
                {
                    ct.AddToEmployees(employee);
                    ct.SaveChanges();
                }
                catch
                {
                    throw;
                }
                return true;
            }
        }

        public bool InsertUser(User user)
        {
            try
            {
                context.AddToUsers(user);
                context.SaveChanges();
            }
            catch
            {
                throw;
            }
            return true;
        }

        public bool InsertGroup(Group group)
        {
            try
            {
                context.AddToGroups(group);
                context.SaveChanges();
            }
            catch
            {
                throw;
            }
            return true;
        }

        public bool InsertMenu(Menu menu)
        {
            try
            {
                context.AddToMenus(menu);
                context.SaveChanges();
            }
            catch
            {
                throw;
            }
            return true;
        }
        public bool InsertMenuFunction(Menu_Function menu_function)
        {
            try
            {
                context.AddToMenu_Function(menu_function);
                context.SaveChanges();
            }
            catch
            {
                throw;
            }
            return true;
        }

        public bool InsertRole(Role role)
        {
            try
            {
                context.AddToRoles(role);
                context.SaveChanges();
            }
            catch
            {
                throw;
            }
            return true;
        }

        public bool InsertSetting(Setting setting)
        {
            try
            {
                context.AddToSettings(setting);
                context.SaveChanges();
            }
            catch
            {
                throw;
            }
            return true;
        }

        public int InsertInvoice(Invoice saleInvoice)
        {
            try
            {
                context.AddToInvoices(saleInvoice);
                context.SaveChanges();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return 1;
        }

        public int InsertInvoice(Invoice invoice, InvoiceDetail [] invoiceDetails)
        {
            int result = 1;
            int nCount = 0;
            DbTransaction dbTran = null;
            try
            {
                context.Connection.Open();
                dbTran = context.Connection.BeginTransaction();
                context.AddToInvoices(invoice);
                foreach (InvoiceDetail d in invoiceDetails)
                {
                    if (d != null)
                    {
                        context.AddToInvoiceDetails(d);
                        nCount++;
                    }                    
                }
                context.SaveChanges();
                if (nCount == 0)
                {
                    Exception et=new Exception("không có chi tiết hóa đơn");
                    //dbTran.Rollback();
                    //result = 0;
                    throw et;
                }
                else
                    dbTran.Commit();
            }
            catch (Exception ex)
            {
                dbTran.Rollback();
                result = 0;
                throw ex;
            }
            finally
            {
                context.Connection.Close();
            }
            return result;
        }

        public static bool UpdateInvoice(Invoice invoice, InvoiceDetail[] invoiceDetails)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                DbTransaction dbTran = null;
                try
                {
                    decimal Amount = 0;
                    //decimal totalAirlineAmount = 0;
                    //decimal totalCommission = 0;
                    //decimal totalAmount = 0;
                    //decimal totalPaidAmount = 0;
                    ct.Connection.Open();
                    dbTran = ct.Connection.BeginTransaction();
                    Invoice inv = (from c in ct.Invoices
                                   where c.code.Equals(invoice.code)
                                   select c).First();
                    //Customer cus = context.Customers.First(i => i.code == customer.code);
                    inv.invoiced_date = invoice.invoiced_date;
                    inv.updated_date = DateTime.Now;
                    inv.customer_code = invoice.customer_code;
                    inv.customer_name = invoice.customer_name;
                    inv.customer_phone = invoice.customer_phone;
                    inv.saleman = invoice.saleman;
                    inv.item_type = invoice.item_type;
                    inv.description = inv.description;
                    inv.amount = Amount;
                    inv.airline_commission = invoice.airline_commission;
                    inv.commission = invoice.commission;
                    inv.total_amount = invoice.total_amount;
                    inv.extended_debt_day_num = invoice.extended_debt_day_num;
                    inv.paid_amount = invoice.paid_amount;
                    inv.payment_method = invoice.payment_method;
                    ct.SaveChanges();

                    foreach (InvoiceDetail d in invoiceDetails)
                    {
                        if (d != null)
                        {
                            InvoiceDetail detail = (from c in ct.InvoiceDetails
                                                    where c.invoice_code.Equals(d.invoice_code) && c.order_num.Equals(d.order_num)
                                                    select c).First();
                            detail.item_code = d.item_code;
                            detail.item_name = d.item_name;
                            detail.journey_code = d.journey_code;
                            detail.airline_code = d.airline_code;
                            detail.ticket_code = d.ticket_code;
                            //detail.quantity=d.quantity
                            detail.amount = d.amount;
                            detail.commission = d.commission;
                            detail.sale_price = d.sale_price;
                            //detail.status_quantity=d.status_quantity;
                            detail.status_amount = d.status_amount;
                            detail.service_fee = d.service_fee;
                            detail.airline_service_fee = d.airline_service_fee;
                            detail.total_amount = d.total_amount;
                            detail.airline_commission = d.airline_commission;
                            detail.passenger_name = d.passenger_name;
                            detail.passenger_phone = d.passenger_phone;
                            detail.passenger_email = d.passenger_email;
                            detail.passenger_id_card = d.passenger_id_card;
                            detail.depart_date = d.depart_date;
                            detail.return_date = d.return_date;
                            detail.currency_code = d.currency_code;
                            detail.rate = d.rate;
                            //detail.status = d.status;
                            //detail.status_invoice=d.status_invoice;
                            detail.description = d.description;
                            detail.rank = d.rank;
                            ct.SaveChanges();
                        }

                    }
                    dbTran.Commit();

                }
                catch
                {
                    dbTran.Rollback();
                    throw;
           
                }
                return true;
            }
        }

        public bool UpdateCustomer(Customer customer)
        {
                try
                {
                  var cus = (from c in context.Customers
                           where c.code.Equals(customer.code)
                           select c).First<Customer>();
                    cus.name = customer.name;
                    cus.mobile = customer.mobile;
                    cus.tax_code = customer.tax_code;
                    cus.telephone = customer.telephone;
                    cus.type = customer.type;
                    cus.fax = customer.fax;
                    cus.email = customer.email;
                    cus.description = customer.description;
                    cus.debt_day_num = customer.debt_day_num;
                    cus.address = customer.address;

                    context.SaveChanges();
                }
                catch
                {
                    throw;
                }
            return true;
        }

        public bool UpdateJourney(Journey journey)
        {
            try
            {
                Journey _journey = (from j in context.Journeys
                                    where j.code.Equals(journey.code)
                                    select j).First<Journey>();

                _journey.description = journey.description;
                _journey.name = journey.name;
                _journey.type = journey.type;
                _journey.deleted = journey.deleted;

                context.SaveChanges();
            }
            catch
            {
                throw;
            }
            return true;
        }

        public bool UpdateAirline(Airline airline)
        {
            try
            {
                Airline _airline= (from a in context.Airlines
                                       where a.code.Equals(airline.code)
                                       select a).First<Airline>();

                _airline.description = airline.description;
                _airline.name = airline.name;
                _airline.type = airline.type;
                _airline.deleted = airline.deleted;

                context.SaveChanges();
            }
            catch
            {
                throw;
            }
            return true;
        }

        public static bool UpdateEmployee(Employee employee)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                try
                {
                    Employee _employee = (from e in ct.Employees
                                          where employee.code.Equals(employee.code)
                                          select e).First<Employee>();

                    _employee.description = employee.description;
                    _employee.name = employee.name;
                    _employee.email = employee.email;
                    _employee.department_code = employee.department_code;
                    _employee.phone = employee.phone;
                    _employee.status = employee.status;
                    _employee.title_code = employee.title_code;
                    _employee.user_code = employee.user_code;
                    _employee.user_code = employee.user_code;

                    ct.SaveChanges();
                }
                catch
                {
                    throw;
                }
                return true;
            }
        }

        public bool UpdateUser(User user)
        {
            try
            {
                User _user = (from u in context.Users
                                  where u.code.Equals(user.code)
                                  select u).First<User>();
                _user.group_id = user.group_id;
                _user.password = user.password;
                _user.status = user.status;

                context.SaveChanges();
            }
            catch {
                throw;
            }

            return true;
        }

        public static bool UpdateAutoCodeByTable(AutoCode autoCode, string tableName)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                try
                {
                    AutoCode _autoCode = (from i in ct.AutoCodes
                                          where i.table_name.Equals(tableName)
                                          select i).First();
                    _autoCode.current_value = autoCode.current_value;
                    ct.SaveChanges();
                }
                catch
                {
                    throw;
                }
                return true;
            }
        }

        public static bool DeleteInvoice(string code)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                DbTransaction dbTran = null;
                try
                {

                    ct.Connection.Open();
                    dbTran = ct.Connection.BeginTransaction();
                    Invoice inv = (from c in ct.Invoices
                                   where c.code.Equals(code)
                                   select c).First();
                    ct.Invoices.DeleteObject(inv);

                    IList<InvoiceDetail> invoiceDetails = (from c in ct.InvoiceDetails
                                                           where c.invoice_code.Equals(code)
                                                           select c).ToList<InvoiceDetail>();
                    foreach (InvoiceDetail d in invoiceDetails)
                    {
                        ct.InvoiceDetails.DeleteObject(d);
                    }

                    ct.SaveChanges();
                    dbTran.Commit();

                }
                catch
                {
                    dbTran.Rollback();
                    throw;
              
                }
                return true;
            }
        }

        public static bool DeleteAirlineByCode(string code)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var i = from a in ct.Airlines
                        where a.code.Equals(code)
                        select a;
                foreach (var j in i)
                {
                    ct.DeleteObject(j);
                }
                try
                {
                    ct.SaveChanges();
                }
                catch (Exception e)
                {
                    throw e;
                }
                return true;
            }
        }

        public static bool DeleteCustomerByCode(string code)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var i = from a in ct.Customers
                        where a.code.Equals(code)
                        select a;
                foreach (var j in i)
                {
                    ct.DeleteObject(j);
                }
                try
                {
                    ct.SaveChanges();
                }
                catch (Exception e)
                {
                    throw e;
                }
                return true;
            }
        }

        public static bool DeleteJourneyByCode(string code)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var i = from a in ct.Journeys
                        where a.code.Equals(code)
                        select a;
                foreach (var j in i)
                {
                    ct.DeleteObject(j);
                }
                try
                {
                    ct.SaveChanges();
                }
                catch (Exception e)
                {
                    throw e;
                }
                return true;
            }
        }

        public static bool DeleteItemByCode(string code)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var i = from a in ct.Items
                        where a.code.Equals(code)
                        select a;
                foreach (var j in i)
                {
                    ct.DeleteObject(j);
                }
                try
                {
                    ct.SaveChanges();
                }
                catch (Exception e)
                {
                    throw e;
                }
                return true;
            }
        }

        public static bool DeleteEmployeeByCode(string code)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var i = from a in ct.Employees
                        where a.code.Equals(code)
                        select a;
                foreach (var j in i)
                {
                    ct.DeleteObject(j);
                }
                try
                {
                    ct.SaveChanges();
                }
                catch (Exception e)
                {
                    throw e;
                }
                return true;
            }
        }
        public static bool DeleteUserByCode(string code)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var i = from a in ct.Users
                        where a.code.Equals(code)
                        select a;
                foreach (var j in i)
                {
                    ct.DeleteObject(j);
                }
                try
                {
                    ct.SaveChanges();
                }
                catch (Exception e)
                {
                    throw e;
                }
                return true;
            }
        }

        public static bool DeleteGroupById(int id)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var i = from a in ct.Groups
                        where a.id.Equals(id)
                        select a;
                foreach (var j in i)
                {
                    ct.DeleteObject(j);
                }
                try
                {
                    ct.SaveChanges();
                }
                catch (Exception e)
                {
                    throw e;
                }
                return true;
            }
        }

        public static bool DeleteSaleInvoiceByCode(string code)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var i = from a in ct.Invoices
                        where a.code.Equals(code)
                        select a;
                foreach (var j in i)
                {
                    ct.DeleteObject(j);
                }
                try
                {
                    ct.SaveChanges();
                }
                catch (Exception e)
                {
                    throw e;
                }
                return true;
            }
        }

        public string connectionString()
        {
            string connect = "";
            ObjectContext ct = context;
            EntityConnection entityConnection = ct.Connection as EntityConnection;
            if (null != entityConnection)
            {
                connect = entityConnection.StoreConnection.ConnectionString;
            }
            return connect;
        }

        public decimal SelectRateWithCurrencyCode(string airlineCode, string currencyCode, DateTime invoiceDate)
        {
            decimal rate = 1;
            //ObjectContext ct = context;
            //EntityConnection entityConnection = ct.Connection as EntityConnection;
            //ObjectParameter rateParm = new ObjectParameter("rate", new decimal());
            //ObjectParameter errorParm = new ObjectParameter("error", new Int32());
            //ObjectParameter error_messageParm = new ObjectParameter("error_message", typeof(String));
            //String[] para_report = new String[3] { "@airlineCode", "@currencyCode", "@invoicedate" };
            //Object[] objValue_report = new Object[3] { airlineCode, currencyCode, invoiceDate };
            //SqlDataAccessHelper helper = new SqlDataAccessHelper();
            //rate = helper.ExecuteNoneQuery("ups_getRateCurrency", para_report, objValue_report);
            try
            {
                //if (context.GetRateCurrency(airlineCode, currencyCode, invoiceDate) == null)
                //    rate = 1;
                //rate = (decimal)context.GetRateCurrency(airlineCode, currencyCode, invoiceDate);
                //System.Nullable<decimal> iReturnValue = context.GetRateCurrency(airlineCode, currencyCode, invoiceDate).SingleOrDefault();
                //if (iReturnValue.HasValue)
                //{
                //    rate = iReturnValue.Value;
                //}
            }
            catch
            {
                throw;
            }

            return rate;
        }

        //public static int CheckExistsInvoiceCode(string InvoiceCode)
        //{
        //    using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
        //    {
        //        int error = 1;
        //        try
        //        {
        //            System.Nullable<int> iReturnValue = ct.CheckInvoiceCode(InvoiceCode).SingleOrDefault();
        //            if (iReturnValue.HasValue)
        //            {
        //                error = iReturnValue.Value;
        //            }
        //        }
        //        catch (Exception e)
        //        {

        //        }
        //        return error;
        //    }
        //}
        public static int CheckExistsInvoiceCode(string InvoiceCode)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                var a = (from m in ct.Invoices
                         where m.code.Equals(InvoiceCode)
                         select m).ToList<Invoice>();
                var b = (from m in ct.InvoiceDetails
                         where m.invoice_code.Equals(InvoiceCode)
                         select m).ToList<InvoiceDetail>();
                if (a.Count > 0 || b.Count > 0)
                {
                    return 1;
                }
                return 0;
            }
        }

        public static string GetCodeCurrent(string tableName, string preFixCode)
        {
            string result = "";
            //ObjectContext ct = context;
            //EntityConnection entityConnection = ct.Connection as EntityConnection;
            //String[] para_report = new String[2] { "@tableName", "@prefix" };
            //Object[] objValue_report = new Object[2] { tableName, preFixCode };
            //SqlDataAccessHelper helper = new SqlDataAccessHelper();
            //result = helper.ExecuteNoneQueryOutputPara("ups_getCurrentCode", para_report, objValue_report).ToString();

            var outputParam = new ObjectParameter("returnValue", typeof(String));
            //var results = context.GetCurrentCode(tableName,preFixCode,outputParam);
            //result = (string)outputParam.Value;

            return result;
        }

        public int SetCodeCurrent(string tableName, string preFixCode, string Code)
        {
            int result = 0;
            ObjectContext ct = context;
            result = context.SetCurrentCode(tableName, preFixCode, Code);
            return result;
        }

        public static bool Login(User user)
        {
            using( HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                List<User> users = (from u in ct.Users
                                    where u.code.Equals(user.code) && u.password.Equals(user.password)
                                    select u).ToList<User>();
                if (users.Count > 0)
                    return true;
                else return false;
            }
        }

        public string GetMaxCode(string tableName, string preFixCode)
        {
            string result = "";
            ObjectContext ct = context;
            //EntityConnection entityConnection = ct.Connection as EntityConnection;
            //String[] para_report = new String[2] { "@tableName", "@prefix" };
            //Object[] objValue_report = new Object[2] { tableName, preFixCode };

            //SqlDataAccessHelper helper = new SqlDataAccessHelper();
            //System.Data.DataTable dt = new System.Data.DataTable();
            //dt = helper.SP_FillDataTable("ups_getMaxCode", para_report, objValue_report);
            //if (dt.Rows.Count > 0)
            //    result = dt.Rows[0][0].ToString();
            //ArrayList arr =new ArrayList();
            //arr=(ArrayList)context.GetMaxCode(tableName,preFixCode);
            //result = context.GetMaxCode(tableName, preFixCode).FirstOrDefault();
            return result;
        }

        //public System.Data.DataTable GetListData(string tableName)
        //{
        //    System.Data.DataTable result = new System.Data.DataTable();
        //    ObjectContext ct = context;
        //    EntityConnection entityConnection = ct.Connection as EntityConnection;
        //    String[] para_report = new String[1] { "@tableName" };
        //    Object[] objValue_report = new Object[1] { tableName };

        //    SqlDataAccessHelper helper = new SqlDataAccessHelper();
        //    result = helper.SP_FillDataTable("usp_GetListData", para_report, objValue_report);
        //    return result;
        //}

        //public ObjectResult<GetListData_Result > GetListData(string tableName)
        //{
        //    ObjectResult<GetListData_Result> result;
        //    result = context.GetListData(tableName);
        //    return result;
        //}

        //public ObjectResult<GetListData_Result> GetListData(string tableName)
        //{
        //    return base.ExecuteFunction<GetListData_Result>(tableName);
        //}


        public  ObjectResult<GetInvoiceOpenList_Result> GetInvoiceIsOpen()
        {
            ObjectResult<GetInvoiceOpenList_Result> result;
            result= context.GetInvoiceOpenList();
            return result;
            
        }

        public System.Data.DataTableCollection GetSalesInvoice(string InvoiceCode)
        {
            DataTableCollection result;//= new DataTableCollection;
            ObjectContext ct = context;
            EntityConnection entityConnection = ct.Connection as EntityConnection;
            String[] para_report = new String[1] { "@InvoiceCode" };
            Object[] objValue_report = new Object[1] { InvoiceCode };

            SqlDataAccessHelper helper = new SqlDataAccessHelper();
            result = helper.SP_FillDataTableReturnCollect("usp_GetSalesInvoice", para_report, objValue_report);
            return result;
        }

        public static IList<uspRpt_BaocaoBanHang_Result> BaoCaoBanHang(DateTime fromDate, DateTime toDate, string CustomerCode)
        {
            using (HDTTransOceanEntities ct = new HDTTransOceanEntities())
            {
                return ct.uspRpt_BaocaoBanHang(fromDate, toDate, CustomerCode).ToList<uspRpt_BaocaoBanHang_Result>();
            }

        }

        //public ObjectResult<GetSalesInvoice_Result> GetSalesInvoice(string InvoiceCode)
        //{
        //    ObjectResult<GetSalesInvoice_Result> result;
        //    result = context.GetSalesInvoice(InvoiceCode);
        //    return result;
        //}

        //public string Test()
        //{ 
        //    using(HDTTransOceanEntities ct = new HDTTransOceanEntities())
        //    {
        //        //return string.Empty;
        //        //string act=ct.getmaxcode("Invoice", "HD").FirstOrDefault();
        //        //return act;

        //        var outputParam = new ObjectParameter("returnValue", typeof(String));
        //        //var outputParam = new ObjectParameter("returnValue", new Object());
        //        var results = context.GetCurrentCode("FormInvoice", "HD", outputParam);
        //        //var resultsList = results.ToList();
        //        var outputValue=(string)outputParam.Value;
        //        return outputValue;

        //    }
        //}

    }
}
